package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import base.DbConnect;
import model.Course;
import model.Department;

public class CourseDao {
    public static int add(Course course) {
		Connection conn = DbConnect.getConnect();
		PreparedStatement stmt = null;
		int res = -1;
		String sql = "insert into course values (?,?,?,?,?)";
		try {
			stmt = (PreparedStatement) conn.prepareStatement(sql);
			stmt.setString(1, course.getCou_id());
			stmt.setString(2, course.getCou_category());
			stmt.setString(3, course.getCou_name());
			stmt.setString(4, course.getCou_credit());
			stmt.setString(5, course.getM_id());
			res = stmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			conn.close();
			stmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return res;
	}

	public static ArrayList<Course> select() {
		Connection conn = DbConnect.getConnect();
		Statement stmt = null;
		ResultSet rs = null;
		ArrayList<Course> arrayList = new ArrayList<Course>();
		
		String sql;
		sql = "SELECT * FROM course";
		try {
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			while (rs.next()) {
				Course course = new Course();
				course.setCou_id(rs.getString("cou_id"));
				course.setCou_name(rs.getString("cou_name"));
				course.setCou_category(rs.getString("cou_category"));
				course.setCou_credit(rs.getString("cou_credit"));
				course.setM_id(rs.getString("m_id"));
				arrayList.add(course);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			conn.close();
			stmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return arrayList;
	}
	
	public static Course select(String cou_id) {
		Connection conn = DbConnect.getConnect();
		Statement stmt = null;
		ResultSet rs = null;
		Course course = new Course();
		String sql;
		sql = "SELECT * FROM course where cou_id = '" + cou_id + "'";
		try {
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			while (rs.next()) {
				course.setCou_id(rs.getString("cou_id"));
				course.setCou_name(rs.getString("cou_name"));
				course.setCou_category(rs.getString("cou_category"));
				course.setCou_credit(rs.getString("cou_credit"));
				course.setM_id(rs.getString("m_id"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			conn.close();
			stmt.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return course;
	}
	
	public static int find(String cou_id) {
		Connection conn = DbConnect.getConnect();
		Statement stmt = null;
		ResultSet rs = null;
		String sql;
		sql = "SELECT * FROM course where cou_id = '" + cou_id + "'";
		try {
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			while (rs.next()) {
				return 1;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			conn.close();
			stmt.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return -1;
	}

	/**
	 * 删除信息
	 * @param department
	 * @return
	 */
	public static int delete(String cou_id) {
		Connection conn = DbConnect.getConnect();
		PreparedStatement stmt = null;
		int res = -1;
		String sql = "delete from course where cou_id = ?";
		try {
			stmt = (PreparedStatement) conn.prepareStatement(sql);
			stmt.setString(1, cou_id);
			res = stmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			conn.close();
			stmt.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return res;
	}
	
	/**
	 * 更新信息
	 * @param department
	 * @return
	 */
	public static int updata(Course course) {
		Connection conn = DbConnect.getConnect();
		PreparedStatement stmt = null;
		int res = -1;
		try {
			String sql = "update course set cou_category = ? ,cou_name = ? , cou_credit = ? ,m_id = ? where cou_id = ?";
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, course.getCou_category());
			stmt.setString(2, course.getCou_name());
			stmt.setString(3, course.getCou_credit());
			stmt.setString(4, course.getM_id());
			stmt.setString(5, course.getCou_id());
			res = stmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			conn.close();
			stmt.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return res;
	}
	
}
